package com.edu.yx.app.APP_01_BaseApp;

import com.edu.yx.common.Constant;
import com.edu.yx.util.SQLUtil;
import org.apache.flink.configuration.Configuration;
import org.apache.flink.runtime.state.hashmap.HashMapStateBackend;
import org.apache.flink.streaming.api.CheckpointingMode;
import org.apache.flink.streaming.api.environment.CheckpointConfig;
import org.apache.flink.streaming.api.environment.StreamExecutionEnvironment;
import org.apache.flink.table.api.bridge.java.StreamTableEnvironment;

public abstract class BaseSqlApp {

    public void init(int port, int p, String ckPathAndJobId) {
        System.setProperty("HADOOP_USER_NAME", "yx");
        Configuration conf = new Configuration();
        conf.setInteger("rest.port", port);
        StreamExecutionEnvironment env = StreamExecutionEnvironment.getExecutionEnvironment(conf);
        env.setParallelism(p);

        env.enableCheckpointing(3000);
        env.setStateBackend(new HashMapStateBackend());
        env.getCheckpointConfig().setCheckpointStorage("hdfs://hadoop102:8020/edu2022/" + ckPathAndJobId);
        env.getCheckpointConfig().setCheckpointingMode(CheckpointingMode.EXACTLY_ONCE);
        env.getCheckpointConfig().setCheckpointTimeout(20 * 1000);
        env.getCheckpointConfig().setMaxConcurrentCheckpoints(1);
        env.getCheckpointConfig().setMinPauseBetweenCheckpoints(500);
        env.getCheckpointConfig().setExternalizedCheckpointCleanup(CheckpointConfig.ExternalizedCheckpointCleanup.RETAIN_ON_CANCELLATION);

        StreamTableEnvironment tEnv = StreamTableEnvironment.create(env);
        // 给sql job起名字
        tEnv.getConfig().getConfiguration().setString("pipeline.name", ckPathAndJobId);

        handle(env, tEnv);

    }

    protected abstract void handle(StreamExecutionEnvironment env, StreamTableEnvironment tEnv);


    //从kafka拿出来 提取出来，专门用来读ods层的数据，将非结构化数据转成表
    public void readOdsDb(StreamTableEnvironment tEnv, String groupId){
        tEnv.executeSql("create table ods_db(" +
                " `database` string, " +
                " `table` string, " +
                " `type` string, " +
                " `ts` bigint, " +
                " `data` map<string, string>, " +
                " `old` map<string, string>, " +
                " `pt` as proctime() " +  // lookup join的时候要用
                ")" + SQLUtil.getKafkaSource(Constant.TOPIC_ODS_DB, groupId));
    }

//    //从mysql读 提取出来，专门读取字典表的数据
//    public void readBaseDic(StreamTableEnvironment tEnv) {
//        tEnv.executeSql("create table base_dic(" +
//                "dic_code string, " +
//                "dic_name string " +
//                ")with(" +
//                " 'connector'='jdbc',  " +
//                " 'url' = 'jdbc:mysql://hadoop162:3306/gmall2022?useSSL=false'," +
//                " 'table-name' = 'base_dic', " +
//                " 'username' = 'root',  " +
//                " 'password' = 'aaaaaa'," +
//                " 'lookup.cache.max-rows' = '10', " +
//                " 'lookup.cache.ttl' = '1 hour' " +
//                ")");
//    }

    //读取试卷信息表test_paper
    public void readTestPaper(StreamTableEnvironment tEnv) {
        tEnv.executeSql("create table test_paper(" +
                "id bigint, " +
                "paper_title string," +
                "course_id bigint" +
                ")with(" +
                " 'connector'='jdbc',  " +
                " 'url' = 'jdbc:mysql://hadoop102:3306/edu?useSSL=false'," +
                " 'table-name' = 'test_paper', " +
                " 'username' = 'root',  " +
                " 'password' = '123456'," +
                " 'lookup.cache.max-rows' = '10', " +
                " 'lookup.cache.ttl' = '1 hour' " +
                ")");
    }

    //读取课程信息表course_info
    public void readCourseInfo(StreamTableEnvironment tEnv) {
        tEnv.executeSql("create table course_info(" +
                "id bigint, " +
                "course_name string," +
                "subject_id bigint" +
                ")with(" +
                " 'connector'='jdbc',  " +
                " 'url' = 'jdbc:mysql://hadoop102:3306/edu?useSSL=false'," +
                " 'table-name' = 'course_info', " +
                " 'username' = 'root',  " +
                " 'password' = '123456'," +
                " 'lookup.cache.max-rows' = '10', " +
                " 'lookup.cache.ttl' = '1 hour' " +
                ")");
    }

    //读取课程信息表video_info
    public void readVideoInfo(StreamTableEnvironment tEnv) {
        tEnv.executeSql("create table video_info(" +
                "id bigint, " +
                "during_sec bigint," +
                "chapter_id bigint" +
                ")with(" +
                " 'connector'='jdbc',  " +
                " 'url' = 'jdbc:mysql://hadoop102:3306/edu?useSSL=false'," +
                " 'table-name' = 'video_info', " +
                " 'username' = 'root',  " +
                " 'password' = '123456'," +
                " 'lookup.cache.max-rows' = '10', " +
                " 'lookup.cache.ttl' = '1 hour' " +
                ")");
    }

    public void readSubjectInfo(StreamTableEnvironment tEnv) {
        tEnv.executeSql("create table subject_info(" +
                "id bigint, " +
                "subject_name string," +
                "category_id bigint" +
                ")with(" +
                " 'connector'='jdbc',  " +
                " 'url' = 'jdbc:mysql://hadoop102:3306/edu?useSSL=false'," +
                " 'table-name' = 'base_subject_info', " +
                " 'username' = 'root',  " +
                " 'password' = '123456'," +
                " 'lookup.cache.max-rows' = '10', " +
                " 'lookup.cache.ttl' = '1 hour' " +
                ")");
    }




}
